// 导入mysql
// npm i mysql
const mysql = require('mysql');

// 创建数据库链接池
const db = mysql.createPool({
    host: '127.0.0.1',
    user: 'root',
    password: 'root',
    database: 'qianduan'
});

let sql, data;
// 测试数据库链接
// [ RowDataPacket { '1': 1 } ] 成功
// db.query('SELECT 1', (err, res) => {
//     if (err) return console.log(err.message);
//     console.log(res);
// });

// 增
sql = "INSERT `student` (`stu_name`, `stu_age`, `stu_sex`) VALUES (?, ?, ?)";
data = ['jackson2', 25, '男'];
db.query(sql, data, (err, results) => {
    if (err) return console.log(err.message);
    if (results.affectedRows) {
        console.log(`插入成功: ${results.insertId}`);
    }
});

// 删
sql = "DELETE FROM `student` WHERE `stu_id` IN (99, 100)";
db.query(sql, (err, results) => {
    if (err) return console.log(err.message);
    if (results.affectedRows) {
        console.log(`删除成功`);
    }
});

// 改
sql = "UPDATE `student` SET ? WHERE `stu_name` = ?";
data = { stu_name: 'bob', stu_age: 100, stu_sex: '女' };
db.query(sql, [data, data.stu_name], (err, results) => {
    if (err) return console.log(err.message);
    if (results.affectedRows) {
        console.log(`修改成功`);
    }
});

// 查
sql = "SELECT * FROM `student` ORDER BY `stu_id`";
db.query(sql, (err, results) => {
    if (err) return console.log(err.message);
    console.log(results);
});

// 添加一个 status 列
sql = "ALTER TABLE `student` ADD COLUMN `status` INT(1) NOT NULL DEFAULT 0 AFTER `stu_sex`";
db.query(sql, (err, results) => {
    if (err) return console.log(err.message);
    console.log(results);
});